Deterministic vs Non-Deterministic Functions in MySQL
In MySQL, functions can be classified as deterministic or non-deterministic based on whether they always return the same result for the same input values.
Always return the same result for the same input.
Examples: ABS(x), UPPER(str), CONCAT(str1, str2), custom UDFs declared DETERMINISTIC.
Can be safely used in indexes, generated columns, and stored function optimizations.
Replication-safe: their results are predictable across master and replica servers.
May return different results for the same input.
Examples: NOW(), RAND(), UUID(), CURRENT_DATE(), custom UDFs not declared DETERMINISTIC.
Cannot be reliably used in indexed generated columns, as results may vary.
Replication caution: non-deterministic functions can cause inconsistencies between master and replicas if evaluated differently.
Deterministic functions allow creating indexes on expressions or generated columns because the output is stable.
Non-deterministic functions cannot be used in indexed expressions, as the index would not reflect consistent values.
Using non-deterministic functions in WHERE clauses or JOINs is fine but may prevent optimization or caching.
In summary: Deterministic functions are predictable and can be optimized, indexed, and safely replicated. Non-deterministic functions may return varying results, cannot be used in indexed generated columns, and require care in replication to ensure consistency across servers.